﻿
namespace LEEAAttendanceControl.Web.Services
{
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.ComponentModel.DataAnnotations;
    using System.Data;
    using System.Linq;
    using System.ServiceModel.DomainServices.EntityFramework;
    using System.ServiceModel.DomainServices.Hosting;
    using System.ServiceModel.DomainServices.Server;
    using LEEAAttendanceControl.Web;
    using System.Web.Security;

    using System.Collections.ObjectModel;

    using System.IO;
    using System.Data.Objects;
    using System.Data.Objects.SqlClient;

    // Implements application logic using the AttendanceDBEntities context.
    // TODO: Add your application logic to these methods or in additional methods.
    // TODO: Wire up authentication (Windows/ASP.NET Forms) and uncomment the following to disable anonymous access
    // Also consider adding roles to restrict access as appropriate.
    // [RequiresAuthentication]
    [EnableClientAccess()]
    public class LEEAService : LinqToEntitiesDomainService<AttendanceDBEntities>
    {
        public IQueryable<HoursSummary> GetHoursSummaries(int companyID, int departmentID, int employeeID
            , DateTime periodFrom, DateTime periodTo)
        {
            List<HoursSummary> summaries = new List<HoursSummary>();

            foreach (Employee emp in this.ObjectContext.Employees.Where(c => (c.CompanyID == companyID || companyID == 0)
                && (c.DepartmentID == departmentID || departmentID == 0)
                && (c.EmployeeID == employeeID || employeeID == 0)))
            {
                HoursSummary summary = new HoursSummary();
                
                summary.EmployeeID = emp.EmployeeID;
                summary.FirstName = emp.FirstName;
                summary.LastName = emp.LastName;

                List<Hour> hours = new List<Hour>();

                foreach (Hour hour in this.ObjectContext.Hours)
                {
                    if (hour.ActualIN == null)
                    {
                        if (hour.EmployeeID == emp.EmployeeID && hour.ActualOUT.Value.Date >= periodFrom
                            && hour.ActualOUT.Value.Date <= periodTo)
                        {
                            hours.Add(hour);
                        }
                    }
                    else
                    {
                        if (hour.EmployeeID == emp.EmployeeID
                        && hour.ActualIN.Value.Date >= periodFrom && hour.ActualIN.Value.Date <= periodTo)
                        {
                            hours.Add(hour);
                        }
                    }
                }

                int total = hours.Sum(hh => hh.RoundedHours) ?? 0;

                summary.Total = total;

                int breaks = hours.Sum(hh => hh.BreakTime) ?? 0;

                summary.Breaks = breaks;

                int exceptions = 0;

                if (this.ObjectContext.ExceptionAssignments.Where(ea => ea.EmployeeID == emp.EmployeeID).Count() > 0)
                {
                    foreach (ExceptionAssignment assignment in
                        this.ObjectContext.ExceptionAssignments.Where(ea => ea.EmployeeID == emp.EmployeeID))
                    {
                        for (int i = 0; i <= assignment.DateTo.Value.Subtract(assignment.DateFrom.Value).Days; i++)
                        {
                            DateTime day = assignment.DateFrom.Value.AddDays(i).Date;

                            if (day >= periodFrom && day <= periodTo)
                            {
                                exceptions += (int)
                                    this.ObjectContext.Exeptions
                                    .Where(ex => ex.ID == assignment.ExceptionID).FirstOrDefault().DPH.Value * 60;
                            }
                        }
                    }

                    summary.Exceptions = exceptions;
                }

                summary.Paid = (total - breaks) + exceptions;

                summary.Overtime = hours.Where(h => h.ActualOvertime > 0).Sum(hh => hh.RoundedOvertime) ?? 0;

                summaries.Add(summary);
            }

            return summaries.OrderBy(e=>e.FirstName).AsQueryable<HoursSummary>();
        }

        //==========================================================================
        //--> Methods for 'ShiftAssignmentSummary' Entity:

        public IQueryable<ShiftAssignmentSummary> GetShiftAssignmentSummaries(int companyid,int departmentid,int employeeid)
        {
            List<ShiftAssignmentSummary> summaries = new List<ShiftAssignmentSummary>();

            var q = from i in ObjectContext.ShiftAssignments
                    join e in ObjectContext.Employees on i.EmployeeID equals e.EmployeeID
                    join c in ObjectContext.Companies on e.CompanyID equals c.ID
                    where (c.ID == companyid || companyid == 0) && (e.DepartmentID == departmentid || departmentid == 0) && (e.EmployeeID == employeeid || employeeid == 0)
                    select i;

            DateTime today = DateTime.Now.Date;

            foreach (ShiftAssignment assign in q)
            {
                if (today >= assign.AssignmentFrom.Value.Date && today <= assign.AssignmentTo.Value.Date)
                {
                    ShiftAssignmentSummary summary = new ShiftAssignmentSummary();

                    Employee employee = this.ObjectContext.Employees.Where(emp =>
                        emp.EmployeeID == assign.EmployeeID.Value).FirstOrDefault();

                    if (summaries.Where(summ => summ.EmployeeID == assign.EmployeeID.Value).Count() <= 0)
                    {
                        summary.EmployeeID = assign.EmployeeID.Value;

                        summary.FirstName = employee.FirstName;
                        summary.LastName = employee.LastName;

                        summary.Department = this.ObjectContext.Departments.Where(dep =>
                            dep.DepartmentID == employee.DepartmentID.Value).FirstOrDefault().DepartmentName;

                        summary.From = assign.AssignmentFrom.Value;
                        summary.To = assign.AssignmentTo.Value;

                        summaries.Add(summary);
                    }
                }
            }

            return summaries.OrderBy(s=>s.FirstName).AsQueryable<ShiftAssignmentSummary>();
        }

        public IQueryable<ShiftAssignmentSummary> GetShiftAssignmentSummaryByEmployeeID(int id)
        {
            List<ShiftAssignmentSummary> summary = new List<ShiftAssignmentSummary>();

            ShiftAssignmentSummary summ = new ShiftAssignmentSummary();

            Employee employee = this.ObjectContext.Employees.Where(emp => emp.EmployeeID == id).FirstOrDefault();

            ShiftAssignment assignment = this.ObjectContext.ShiftAssignments.Where(sa =>
                sa.EmployeeID == id).FirstOrDefault();

            summ.EmployeeID = id;

            summ.FirstName = employee.FirstName;
            summ.LastName = employee.LastName;

            summ.Department = this.ObjectContext.Departments.Where(dep =>
                dep.DepartmentID == employee.DepartmentID.Value).FirstOrDefault().DepartmentName;

            foreach (ShiftAssignment assign in this.ObjectContext.ShiftAssignments.Where(sa => sa.EmployeeID == id))
            {
                if (DateTime.Now.Date >= assign.AssignmentFrom.Value.Date
                    && DateTime.Now.Date <= assign.AssignmentTo.Value.Date)
                {
                    summ.From = assignment.AssignmentFrom.Value;

                    summ.To = assignment.AssignmentTo.Value;
                }
            }

            summary.Add(summ);

            return summary.OrderBy(s=>s.FirstName).AsQueryable<ShiftAssignmentSummary>();
        }

        public void DeleteShiftAssignmentSummary(ShiftAssignmentSummary summary)
        {
            DateTime today = DateTime.Now.Date;

            foreach (ShiftAssignment assign in this.ObjectContext.ShiftAssignments.Where(sa =>
                sa.EmployeeID == summary.EmployeeID))
            {
                if (today >= assign.AssignmentFrom.Value.Date && today <= assign.AssignmentTo.Value.Date)
                {
                    foreach (ShiftAssignmentDay day in this.ObjectContext.ShiftAssignmentDays.Where(sad
                    => sad.ShiftAssignmentID == assign.ID))
                    {
                        this.ObjectContext.ShiftAssignmentDays.DeleteObject(day);
                    }

                    this.ObjectContext.ShiftAssignments.DeleteObject(assign);
                }
            }

            this.ObjectContext.SaveChanges();
        }

        //==========================================================================
        //--> Methods for 'ShiftDays' Entity:

        public IQueryable<ShiftDays> GetShiftDays(int id)
        {
            List<ShiftDays> shifts = new List<ShiftDays>();

            DateTime today = DateTime.Now.Date;

            foreach (ShiftAssignment assign in this.ObjectContext.ShiftAssignments.Where(sa => sa.EmployeeID == id))
            {
                if (today >= assign.AssignmentFrom.Value.Date && today <= assign.AssignmentTo.Value.Date)
                {
                    ShiftDays days = new ShiftDays();

                    Shift shift = this.ObjectContext.Shifts.Where(sh =>
                        sh.ID == assign.ShiftID.Value).FirstOrDefault();

                    days.AssignmentID = assign.ID;

                    days.EmployeeID = assign.EmployeeID.Value;

                    days.ShiftID = assign.ShiftID.Value;
                    days.ShiftName = shift.ShiftName;

                    days.Color = shift.Color;

                    days.ForeColor = shift.ForeColor;

                    days.From = assign.AssignmentFrom.Value;
                    days.To = assign.AssignmentTo.Value;

                    foreach (ShiftAssignmentDay day in this.ObjectContext.ShiftAssignmentDays.Where(ad
                        => ad.ShiftAssignmentID == assign.ID))
                    {
                        switch (day.DayID)
                        {
                            case 1:
                                days.Saturday = 0.7;
                                break;
                            case 2:
                                days.Sunday = 0.7;
                                break;
                            case 3:
                                days.Monday = 0.7;
                                break;
                            case 4:
                                days.Tuesday = 0.7;
                                break;
                            case 5:
                                days.Wednesday = 0.7;
                                break;
                            case 6:
                                days.Thursday = 0.7;
                                break;
                            case 7:
                                days.Friday = 0.7;
                                break;
                        }
                    }

                    shifts.Add(days);
                }
            }

            return shifts.AsQueryable<ShiftDays>();
        }

        //==========================================================================
        //--> Methods for 'ExceptionAssignmentSummary' Entity:

        public IQueryable<ExceptionAssignmentSummary> GetExceptionAssignmentSummaries(int companyid, int departmentid
            , int employeeid)
        {
            List<ExceptionAssignmentSummary> summaries = new List<ExceptionAssignmentSummary>();

            var q=from i in ObjectContext.ExceptionAssignments
                  join e in ObjectContext.Employees on i.EmployeeID equals e.EmployeeID
                  join c in ObjectContext.Companies on e.CompanyID equals c.ID
                  where (c.ID==companyid||companyid==0)&&(e.DepartmentID==departmentid||departmentid==0)&&(e.EmployeeID==employeeid||employeeid==0)
                  select i;
                    
                    
            foreach (ExceptionAssignment assign in q)
            {
                ExceptionAssignmentSummary summary = new ExceptionAssignmentSummary();

                Employee employee = this.ObjectContext.Employees.Where(emp =>
                    emp.EmployeeID == assign.EmployeeID.Value).FirstOrDefault();

                if (summaries.Where(summ => summ.EmployeeID == employee.EmployeeID).Count() <= 0)
                {
                    summary.EmployeeID = employee.EmployeeID;

                    summary.FirstName = employee.FirstName;
                    summary.LastName = employee.LastName;

                    summary.Department = this.ObjectContext.Departments.Where(dep =>
                        dep.DepartmentID == employee.DepartmentID.Value).FirstOrDefault().DepartmentName;

                    summaries.Add(summary);
                }
            }

            return summaries.OrderBy(s=>s.FirstName).AsQueryable<ExceptionAssignmentSummary>();
        }

        public IQueryable<ExceptionAssignmentSummary> GetExceptionAssignmentSummaryByEmployeeID(int id)
        {
            List<ExceptionAssignmentSummary> summaries = new List<ExceptionAssignmentSummary>();

            ExceptionAssignmentSummary summary = new ExceptionAssignmentSummary();

            Employee employee = this.ObjectContext.Employees.Where(emp => emp.EmployeeID == id).FirstOrDefault();

            summary.EmployeeID = employee.EmployeeID;

            summary.FirstName = employee.FirstName;
            summary.LastName = employee.LastName;

            summary.Department = this.ObjectContext.Departments.Where(dep =>
                dep.DepartmentID == employee.DepartmentID.Value).FirstOrDefault().DepartmentName;

            summaries.Add(summary);

            return summaries.OrderBy(s=>s.FirstName).AsQueryable<ExceptionAssignmentSummary>();
        }

        public void DeleteExceptionAssignmentSummary(ExceptionAssignmentSummary summary)
        {
            foreach (ExceptionAssignment assignment in this.ObjectContext.ExceptionAssignments.Where(ea =>
                ea.EmployeeID == summary.EmployeeID))
            {
                this.ObjectContext.ExceptionAssignments.DeleteObject(assignment);
            }

            this.ObjectContext.SaveChanges();
        }

        //==========================================================================
        //--> Methods for 'ExceptionDays' Entity:

        public IQueryable<ExceptionDays> GetExceptionDays(int id, DateTime from, DateTime to)
        {
            List<ExceptionDays> exceptions = new List<ExceptionDays>();

            foreach (ExceptionAssignment assign in this.ObjectContext.ExceptionAssignments.Where(es =>
                es.EmployeeID == id))
            {
                if (assign.DateFrom.Value.Date >= from && assign.DateFrom.Value.Date <= to)
                {
                    exceptions.Add(new ExceptionDays()
                    {
                        AssignmentID = assign.ID,

                        EmployeeID = id,

                        ExceptionID = assign.ExceptionID.Value,
                        ExceptionName = this.ObjectContext.Exeptions.Where(ex =>
                            ex.ID == assign.ExceptionID.Value).FirstOrDefault().Name,

                        From = assign.DateFrom.Value,
                        To = assign.DateTo.Value
                    });
                }
            }

            return exceptions.AsQueryable<ExceptionDays>();
        }

        //==========================================================================
        //--> Methods for 'UserSummary' Entity:
        public IQueryable<UserSummary> GetUserSummaries()
        {
            return from user in this.ObjectContext.Users select new UserSummary()
                {
                    ID = user.UserID,

                    FirstName = user.FirstName,
                    LastName = user.LastName,

                    Role = user.Role1.RoleName,

                    Company = user.CompanyID.HasValue
                        ? (this.ObjectContext.Companies.Where(com => com.ID == user.CompanyID).FirstOrDefault().CompanyName)
                        : String.Empty,
                    Department = user.DepartmentID.HasValue
                        ? (this.ObjectContext.Departments.Where(dep => dep.DepartmentID == user.DepartmentID).FirstOrDefault().DepartmentName)
                        : String.Empty,

                    Active = user.Active.Value
                };
        }

        public void DeleteUserSummary(UserSummary user)
        {
            this.ObjectContext.Users.DeleteObject(this.ObjectContext.Users.Where(u => u.UserID == user.ID).FirstOrDefault());
        }

        //==========================================================================
        //--> Methods for 'EmployeeSummary' Entity:

        public IQueryable<EmployeeSummary> GetEmployeeSummaries()
        {
            return from emp in this.ObjectContext.Employees orderby emp.FirstName select new EmployeeSummary()
                       {
                           EmployeeID = emp.EmployeeID,

                           ID = emp.EmployeeCode.Value,

                           FirstName = emp.FirstName,
                           LastName = emp.LastName,

                           DepartmentID = emp.Department.DepartmentID,
                           Department = emp.Department.DepartmentName
                       };
        }

        public IQueryable<EmployeeSummary> GetEmployeeSummariesForShifts(int companyid, int departmentid, int employeeid)
        {
           return from e in ObjectContext.Employees
                   join c in ObjectContext.Companies on e.CompanyID equals c.ID
                   where (c.ID == companyid || companyid == 0) && (e.DepartmentID == departmentid || departmentid == 0)
                       && (e.EmployeeID == employeeid || employeeid == 0)
                   orderby e.FirstName
                   select new EmployeeSummary
                   {
                       EmployeeID = e.EmployeeID,
                       ID =e.EmployeeCode,
                       FirstName = e.FirstName,
                       LastName = e.LastName,
                       DepartmentID = e.DepartmentID.Value,
                       Department = e.Department.DepartmentName
                   };
        }

        public IQueryable<EmployeeSummary> GetEmployeeSummariesForExceptions(int companyid,int departmentid,int employeeid)
        {
            
            return   from e in ObjectContext.Employees 
                join c in ObjectContext.Companies on e.CompanyID equals c.ID
                where (c.ID == companyid || companyid == 0) && (e.DepartmentID == departmentid || departmentid == 0)
                    && (e.EmployeeID == employeeid || employeeid == 0)
                orderby e.FirstName
                select new EmployeeSummary
                    {
                        EmployeeID = e.EmployeeID,
                        ID = e.EmployeeCode.Value,
                        FirstName = e.FirstName,
                        LastName = e.LastName,
                        DepartmentID = e.DepartmentID.Value,
                        Department = e.Department.DepartmentName
                    };

        }

        public IQueryable<EmployeeSummary> GetFilteredEmployeeSummaries(int companyID, int departmentID, int employeeID)
        {
            return from employee in this.ObjectContext.Employees.Where(emp => (emp.CompanyID == companyID || companyID == 0)
                       && (emp.DepartmentID == departmentID || departmentID == 0)
                       && (emp.EmployeeID == employeeID || employeeID == 0))
                       orderby(employee.FirstName)
                   select new EmployeeSummary()
                   {
                       EmployeeID = employee.EmployeeID,

                       ID = employee.EmployeeCode.Value,

                       FirstName = employee.FirstName,
                       LastName = employee.LastName,

                       DepartmentID = employee.Department.DepartmentID,
                       Department = employee.Department.DepartmentName
                   };
        }

        /// <summary>
        /// This method only for making employeesummary class as editable
        /// </summary>
        /// <param name="employeesummary"></param>
        [Update]
        public void UpdateEmployeeSummary(EmployeeSummary employeesummary) { }

        //==========================================================================

        // TODO:
        // Consider constraining the results of your query method.  If you need additional input you can
        // add parameters to this method or create additional query methods with different names.
        // To support paging you will need to add ordering to the 'Attendances' query.
        public IQueryable<Attendance> GetAttendances()
        {
            return this.ObjectContext.Attendances;
        }

        public void InsertAttendance(Attendance attendance)
        {
            if ((attendance.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(attendance, EntityState.Added);
            }
            else
            {
                this.ObjectContext.Attendances.AddObject(attendance);
            }
        }

        public void UpdateAttendance(Attendance currentAttendance)
        {
            this.ObjectContext.Attendances.AttachAsModified(currentAttendance, this.ChangeSet.GetOriginal(currentAttendance));
        }

        public void DeleteAttendance(Attendance attendance)
        {
            if ((attendance.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(attendance, EntityState.Deleted);
            }
            else
            {
                this.ObjectContext.Attendances.Attach(attendance);
                this.ObjectContext.Attendances.DeleteObject(attendance);
            }
        }

        // TODO:
        // Consider constraining the results of your query method.  If you need additional input you can
        // add parameters to this method or create additional query methods with different names.
        // To support paging you will need to add ordering to the 'Punches' query.
        public IQueryable<Punch> GetPunches()
        {
            return this.ObjectContext.Punches;
        }

        public IQueryable<TodayINSummary> GetTodayINSummaries(int companyID, DateTime day)
        {
            List<TodayINSummary> summaries = new List<TodayINSummary>();

            foreach (Punch punch in this.ObjectContext.Punches.Where(p => p.PunchType == "IN"
                && this.ObjectContext.Employees.Where(emp => emp.CompanyID == companyID || companyID == 0)
                    .Contains(this.ObjectContext.Employees.Where(e => e.EmployeeID == p.EmployeeID).FirstOrDefault())
                && p.DataTime.Value.Day == day.Day && p.DataTime.Value.Month == day.Month
                && p.DataTime.Value.Year == day.Year))
            {
                Employee employee = this.ObjectContext.Employees.Where(emp
                    => emp.EmployeeID == punch.EmployeeID).First();

                TodayINSummary summary = new TodayINSummary();

                summary.EmployeeID = employee.EmployeeID;

                summary.FirstName = employee.FirstName;
                summary.LastName = employee.LastName;

                summary.Department = this.ObjectContext.Departments.Where(dep
                    => dep.DepartmentID == employee.DepartmentID).FirstOrDefault().DepartmentName;

                summary.INTime = punch.DataTime.Value;

                summaries.Add(summary);
            }

            return summaries.OrderBy(s=>s.FirstName).AsQueryable<TodayINSummary>();
        }

        public string CountAttendantsNumber(int companyID, DateTime day)
        {
            string numbers = String.Empty;

            IEnumerable<Punch> punches = this.ObjectContext.Punches.Where(p => p.PunchType == "IN"
                && this.ObjectContext.Employees.Where(emp => emp.CompanyID == companyID || companyID == 0)
                    .Contains(this.ObjectContext.Employees.Where(e => e.EmployeeID == p.EmployeeID).FirstOrDefault())
                && p.DataTime.Value.Day == day.Day && p.DataTime.Value.Month == day.Month
                && p.DataTime.Value.Year == day.Year).AsEnumerable<Punch>();

            numbers = punches.Count().ToString();

            numbers += "|";

            numbers += this.ObjectContext.Employees.Where(emp
                => (emp.CompanyID.Value == companyID || companyID == 0) && punches.Where(p
                    => p.EmployeeID == emp.EmployeeID).Count() == 0).Count().ToString();

            return numbers;
        }

        public IQueryable<Employee> GetLateEmployees(int companyID, DateTime day)
        {
            List<Employee> employees = new List<Employee>();

            IEnumerable<Punch> punches = this.ObjectContext.Punches.Where(p => p.PunchType == "IN"
                && this.ObjectContext.Employees.Where(emp => emp.CompanyID == companyID || companyID == 0)
                    .Contains(this.ObjectContext.Employees.Where(e => e.EmployeeID == p.EmployeeID).FirstOrDefault())
                && p.DataTime.Value.Day == day.Day && p.DataTime.Value.Month == day.Month
                && p.DataTime.Value.Year == day.Year).AsEnumerable<Punch>();

            foreach (Employee employee in this.ObjectContext.Employees.Include("Department").Where(emp
                => (emp.CompanyID.Value == companyID || companyID == 0) && punches.Where(p
                    => p.EmployeeID == emp.EmployeeID).Count() == 0))
            {
                employees.Add(employee);
            }

            return employees.OrderBy(e=>e.FirstName).AsQueryable<Employee>();
        }

        public void InsertPunch(Punch punch)
        {
            if ((punch.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(punch, EntityState.Added);
            }
            else
            {
                this.ObjectContext.Punches.AddObject(punch);
            }
        }

        public void UpdatePunch(Punch currentPunch)
        {
            this.ObjectContext.Punches.AttachAsModified(currentPunch, this.ChangeSet.GetOriginal(currentPunch));
        }

        public void DeletePunch(Punch punch)
        {
            if ((punch.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(punch, EntityState.Deleted);
            }
            else
            {
                this.ObjectContext.Punches.Attach(punch);
                this.ObjectContext.Punches.DeleteObject(punch);
            }
        }

        // TODO:
        // Consider constraining the results of your query method.  If you need additional input you can
        // add parameters to this method or create additional query methods with different names.
        // To support paging you will need to add ordering to the 'WorkDays' query.
        public IQueryable<WorkDay> GetWorkDays()
        {
            return this.ObjectContext.WorkDays;
        }

        public void InsertWorkDay(WorkDay workDay)
        {
            if ((workDay.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(workDay, EntityState.Added);
            }
            else
            {
                this.ObjectContext.WorkDays.AddObject(workDay);
            }
        }

        public void UpdateWorkDay(WorkDay currentWorkDay)
        {
            this.ObjectContext.WorkDays.AttachAsModified(currentWorkDay, this.ChangeSet.GetOriginal(currentWorkDay));
        }

        public void DeleteWorkDay(WorkDay workDay)
        {
            if ((workDay.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(workDay, EntityState.Deleted);
            }
            else
            {
                this.ObjectContext.WorkDays.Attach(workDay);
                this.ObjectContext.WorkDays.DeleteObject(workDay);
            }
        }

        // TODO:
        // Consider constraining the results of your query method.  If you need additional input you can
        // add parameters to this method or create additional query methods with different names.
        // To support paging you will need to add ordering to the 'Changes' query.
        public IQueryable<Change> GetChanges()
        {
            return this.ObjectContext.Changes;
        }

        public void InsertChange(Change change)
        {
            if ((change.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(change, EntityState.Added);
            }
            else
            {
                this.ObjectContext.Changes.AddObject(change);
            }
        }

        public void UpdateChange(Change currentChange)
        {
            this.ObjectContext.Changes.AttachAsModified(currentChange, this.ChangeSet.GetOriginal(currentChange));
        }

        public void DeleteChange(Change change)
        {
            if ((change.EntityState == EntityState.Detached))
            {
                this.ObjectContext.Changes.Attach(change);
            }
            this.ObjectContext.Changes.DeleteObject(change);
        }

        // TODO:
        // Consider constraining the results of your query method.  If you need additional input you can
        // add parameters to this method or create additional query methods with different names.
        // To support paging you will need to add ordering to the 'Companies' query.
        public IQueryable<Company> GetCompanies()
        {
            return this.ObjectContext.Companies;
        }

        public void DestroyCompanyById(int id)
        {
            this.ObjectContext.Companies.DeleteObject(this.ObjectContext.Companies.Where(com => com.ID == id).FirstOrDefault());

            this.ObjectContext.SaveChanges();
        }

        public int? GetCompanyMaxId()
        {
            return (from c in this.ObjectContext.Companies
                    select c.CompanyID).Max() +1;
        }

        public IQueryable<Company> GetCompanyById(int Id)
        {
            return this.ObjectContext.Companies.Where(c => c.ID == Id);
        }

        public void InsertCompany(Company company)
        {
            if ((company.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(company, EntityState.Added);
            }
            else
            {
                this.ObjectContext.Companies.AddObject(company);
            }
        }

        public void UpdateCompany(Company currentCompany)
        {
            this.ObjectContext.Companies.AttachAsModified(currentCompany, this.ChangeSet.GetOriginal(currentCompany));
        }

        public void DeleteCompany(Company company)
        {
            if ((company.EntityState == EntityState.Detached))
            {
                this.ObjectContext.Companies.Attach(company);
            }
            this.ObjectContext.Companies.DeleteObject(company);
        }

        // TODO:
        // Consider constraining the results of your query method.  If you need additional input you can
        // add parameters to this method or create additional query methods with different names.
        // To support paging you will need to add ordering to the 'DailyOvertimeApprovals' query.
        public IQueryable<DailyOvertimeApproval> GetDailyOvertimeApprovals()
        {
            return this.ObjectContext.DailyOvertimeApprovals;
        }

        public void InsertDailyOvertimeApproval(DailyOvertimeApproval dailyOvertimeApproval)
        {
            if ((dailyOvertimeApproval.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(dailyOvertimeApproval, EntityState.Added);
            }
            else
            {
                this.ObjectContext.DailyOvertimeApprovals.AddObject(dailyOvertimeApproval);
            }
        }

        public void UpdateDailyOvertimeApproval(DailyOvertimeApproval currentDailyOvertimeApproval)
        {
            this.ObjectContext.DailyOvertimeApprovals.AttachAsModified(currentDailyOvertimeApproval, this.ChangeSet.GetOriginal(currentDailyOvertimeApproval));
        }

        public void DeleteDailyOvertimeApproval(DailyOvertimeApproval dailyOvertimeApproval)
        {
            if ((dailyOvertimeApproval.EntityState == EntityState.Detached))
            {
                this.ObjectContext.DailyOvertimeApprovals.Attach(dailyOvertimeApproval);
            }
            this.ObjectContext.DailyOvertimeApprovals.DeleteObject(dailyOvertimeApproval);
        }

        // TODO:
        // Consider constraining the results of your query method.  If you need additional input you can
        // add parameters to this method or create additional query methods with different names.
        // To support paging you will need to add ordering to the 'Departments' query.
        public IQueryable<Department> GetDepartments()
        {
            return this.ObjectContext.Departments;
        }

        public IQueryable<Department> GetDepartmentsForCompany(int id)
        {
            return this.ObjectContext.Departments.Where(dep => dep.CompanyID.Value == id); ;
        }

        public void DestroyDepartmentById(int id)
        {
            this.ObjectContext.Departments.DeleteObject(this.ObjectContext.Departments.Where(dep
                => dep.DepartmentID == id).FirstOrDefault());

            this.ObjectContext.SaveChanges();
        }

        public int? GetDepartmentMaxCode()
        {
            return (from c in this.ObjectContext.Departments
                    select c.DepartmentCode).Max() + 1;
        }

        public IQueryable<Department> GetDepartmentByDepartmentId(int departmentId)
        {
            return this.ObjectContext.Departments.Where(d => d.DepartmentID == departmentId);
        }

        public IQueryable<Department> GetDepartmentsWithCompany()
        {
            return this.ObjectContext.Departments.Include("Company");
        }

        public IQueryable<Department> GetDepartmentsByCompanyId(int companyID)
        {
            return this.ObjectContext.Departments.Where(dep => dep.CompanyID == companyID);
        }

        public void InsertDepartment(Department department)
        {
            if ((department.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(department, EntityState.Added);
            }
            else
            {
                this.ObjectContext.Departments.AddObject(department);
            }
        }

        public void UpdateDepartment(Department currentDepartment)
        {
            this.ObjectContext.Departments.AttachAsModified(currentDepartment, this.ChangeSet.GetOriginal(currentDepartment));
        }

        public void DeleteDepartment(Department department)
        {
            if ((department.EntityState == EntityState.Detached))
            {
                this.ObjectContext.Departments.Attach(department);
            }
            this.ObjectContext.Departments.DeleteObject(department);
        }

        // TODO:
        // Consider constraining the results of your query method.  If you need additional input you can
        // add parameters to this method or create additional query methods with different names.
        // To support paging you will need to add ordering to the 'Employees' query.
        public IQueryable<Employee> GetEmployees()
        {
            return this.ObjectContext.Employees.OrderBy(e=>e.FirstName);
        }

        public IQueryable<Employee> GetEmployeeByEmployeeID(int EmployeeId)
        {
            return this.ObjectContext.Employees.Where(e => e.EmployeeID == EmployeeId);
        }

        public IQueryable<Employee> GetEmployeesWithCompanyAndDepartmentsAndEmployeeId(int companyid
            , int departmentid, int employeeid)
        {
            return this.ObjectContext.Employees.Include("Company").Include("Department").Where(c
                => (c.CompanyID == companyid || companyid == 0) && (c.DepartmentID == departmentid || departmentid == 0)
                    && (c.EmployeeID == employeeid || employeeid == 0)).OrderBy(e => e.FirstName);
        }

        public int? GetEmployeeMaxCode()
        {
            return (from c in this.ObjectContext.Employees
                    select c.EmployeeCode).Max() + 1;
        }

        public void InsertEmployee(Employee employee)
        {
            if ((employee.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(employee, EntityState.Added);
            }
            else
            {
                this.ObjectContext.Employees.AddObject(employee);
            }
        }

        public void UpdateEmployee(Employee currentEmployee)
        {
            this.ObjectContext.Employees.AttachAsModified(currentEmployee, this.ChangeSet.GetOriginal(currentEmployee));
        }

        public void DeleteEmployee(Employee employee)
        {
            if ((employee.EntityState == EntityState.Detached))
            {
                this.ObjectContext.Employees.Attach(employee);
            }
            this.ObjectContext.Employees.DeleteObject(employee);
        }

        // TODO:
        // Consider constraining the results of your query method.  If you need additional input you can
        // add parameters to this method or create additional query methods with different names.
        // To support paging you will need to add ordering to the 'ExceptionAssignments' query.
        public IQueryable<ExceptionAssignment> GetExceptionAssignments()
        {
            return this.ObjectContext.ExceptionAssignments;
        }

        public ExceptionAssignment GetExceptionAssignmentByID(int id)
        {
            return this.ObjectContext.ExceptionAssignments.Include("Exeption").Where(ea => ea.ID == id).FirstOrDefault();
        }

        public void DestroyExceptionAssignmentByID(int exAssID)
        {
            ExceptionAssignment exAss = this.ObjectContext.ExceptionAssignments.Where(ea
                => ea.ID == exAssID).FirstOrDefault();

            this.ObjectContext.ExceptionAssignments.DeleteObject(exAss);

            this.ObjectContext.SaveChanges();
        }

        /*
        public void DestroyExceptionAssignmentByEmployeeID(int id)
        {
            foreach (ExceptionAssignment assignment in this.ObjectContext.ExceptionAssignments.Where(ea =>
                ea.EmployeeID == id))
            {
                this.ObjectContext.ExceptionAssignments.DeleteObject(assignment);
            }

            this.ObjectContext.SaveChanges();
        }
        */

        public void InsertExceptionAssignment(ExceptionAssignment exceptionAssignment)
        {
            if ((exceptionAssignment.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(exceptionAssignment, EntityState.Added);
            }
            else
            {
                this.ObjectContext.ExceptionAssignments.AddObject(exceptionAssignment);
            }
        }

        public void UpdateExceptionAssignment(ExceptionAssignment currentExceptionAssignment)
        {
            this.ObjectContext.ExceptionAssignments.AttachAsModified(currentExceptionAssignment, this.ChangeSet.GetOriginal(currentExceptionAssignment));
        }

        public void DeleteExceptionAssignment(ExceptionAssignment exceptionAssignment)
        {
            if ((exceptionAssignment.EntityState == EntityState.Detached))
            {
                this.ObjectContext.ExceptionAssignments.Attach(exceptionAssignment);
            }
            this.ObjectContext.ExceptionAssignments.DeleteObject(exceptionAssignment);
        }

        // TODO:
        // Consider constraining the results of your query method.  If you need additional input you can
        // add parameters to this method or create additional query methods with different names.
        // To support paging you will need to add ordering to the 'Exeptions' query.
        public IQueryable<Exeption> GetExeptions()
        {
            return this.ObjectContext.Exeptions;
        }

        public IQueryable<Exeption> GetExceptionById(int id)
        {
            return this.ObjectContext.Exeptions.Where(ex => ex.ID == id);
        }

        public IQueryable<Exeption> GetExceptionsByCompanyID(int companyID)
        {
            return this.ObjectContext.Exeptions.Where(ex => ex.CompanyID == companyID);
        }

        public void InsertExeption(Exeption exeption)
        {
            if ((exeption.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(exeption, EntityState.Added);
            }
            else
            {
                this.ObjectContext.Exeptions.AddObject(exeption);
            }
        }

        public void UpdateExeption(Exeption currentExeption)
        {
            this.ObjectContext.Exeptions.AttachAsModified(currentExeption, this.ChangeSet.GetOriginal(currentExeption));
        }

        public void DeleteExeption(Exeption exeption)
        {
            if ((exeption.EntityState == EntityState.Detached))
            {
                this.ObjectContext.Exeptions.Attach(exeption);
            }
            this.ObjectContext.Exeptions.DeleteObject(exeption);
        }

        // TODO:
        // Consider constraining the results of your query method.  If you need additional input you can
        // add parameters to this method or create additional query methods with different names.
        // To support paging you will need to add ordering to the 'Hours' query.
        public IQueryable<Hour> GetHours()
        {
            return this.ObjectContext.Hours;
        }

        public IQueryable<Hour> GetHourById(int hoursID)
        {
            return this.ObjectContext.Hours.Where(h => h.HoursID == hoursID);
        }

        public IQueryable<Hour> GetHoursByEmployeeID(int empID, DateTime periodFrom, DateTime periodTo)
        {
            List<Hour> hours = new List<Hour>();

            foreach (Hour hour in this.ObjectContext.Hours)
            {
                if (hour.ActualIN == null)
                {
                    if (hour.EmployeeID == empID && hour.ActualOUT.Value.Date >= periodFrom
                        && hour.ActualOUT.Value.Date <= periodTo)
                    {
                        hours.Add(hour);
                    }
                }
                else
                {
                    if (hour.EmployeeID == empID && hour.ActualIN.Value.Date >= periodFrom
                    && hour.ActualIN.Value.Date <= periodTo)
                    {
                        hours.Add(hour);
                    }
                }
            }

            return hours.OrderBy(hh => hh.ActualIN).AsQueryable<Hour>();
        }

        public IQueryable<Hour> GetOvertimeByEmployeeID(int empID, DateTime periodFrom, DateTime periodTo)
        {
            List<Hour> hours = new List<Hour>();

            foreach (Hour hour in this.ObjectContext.Hours.Where(h => h.EmployeeID == empID))
            {
                if (hour.ActualIN != null && hour.ActualOUT != null && hour.ActualOUT.Value.Date >= periodFrom
                    && hour.ActualOUT.Value.Date <= periodTo && hour.ActualOvertime != null && hour.ActualOvertime.Value > 0)
                {
                    hours.Add(hour);
                }
            }

            return hours.OrderBy(hh => hh.RoundedIN).AsQueryable<Hour>();
        }

        public void DestroyHoursByID(int id)
        {
            var hour=this.ObjectContext.Hours.Where(h => h.HoursID == id).First();
            this.ObjectContext.Hours.DeleteObject(hour);
            if(hour.PunchINID!=null)
               this.ObjectContext.Punches.DeleteObject(this.ObjectContext.Punches.Where(p => p.PunchID == hour.PunchINID).First());
            if(hour.PunchOUTID!=null)
               this.ObjectContext.Punches.DeleteObject(this.ObjectContext.Punches.Where(p => p.PunchID == hour.PunchOUTID).First());
            this.ObjectContext.SaveChanges();
        }

        public void InsertHour(Hour hour)
        {
            if ((hour.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(hour, EntityState.Added);
            }
            else
            {
                this.ObjectContext.Hours.AddObject(hour);
            }
        }

        public void UpdateHour(Hour currentHour)
        {
            this.ObjectContext.Hours.AttachAsModified(currentHour, this.ChangeSet.GetOriginal(currentHour));
        }

        public void DeleteHour(Hour hour)
        {
            if ((hour.EntityState == EntityState.Detached))
            {
                this.ObjectContext.Hours.Attach(hour);
            }
            this.ObjectContext.Hours.DeleteObject(hour);
        }

        // TODO:
        // Consider constraining the results of your query method.  If you need additional input you can
        // add parameters to this method or create additional query methods with different names.
        // To support paging you will need to add ordering to the 'Jobs' query.
        public IQueryable<Job> GetJobs()
        {
            return this.ObjectContext.Jobs;
        }

        public IQueryable<Job> GetJobById(int id)
        {
            return this.ObjectContext.Jobs.Where(job => job.JobID == id);
        }

        public int? GetJobMaxId()
        {
            int? max = (from job in this.ObjectContext.Jobs select job.CustomJobID).Max();

            return (max == null ? 0 : max) + 1;
        }

        public void InsertJob(Job job)
        {
            if ((job.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(job, EntityState.Added);
            }
            else
            {
                this.ObjectContext.Jobs.AddObject(job);
            }
        }

        public void UpdateJob(Job currentJob)
        {
            this.ObjectContext.Jobs.AttachAsModified(currentJob, this.ChangeSet.GetOriginal(currentJob));
        }

        public void DeleteJob(Job job)
        {
            if ((job.EntityState == EntityState.Detached))
            {
                this.ObjectContext.Jobs.Attach(job);
            }
            this.ObjectContext.Jobs.DeleteObject(job);
        }

        // TODO:
        // Consider constraining the results of your query method.  If you need additional input you can
        // add parameters to this method or create additional query methods with different names.
        // To support paging you will need to add ordering to the 'Roles' query.
        public IQueryable<Role> GetRoles()
        {
            return this.ObjectContext.Roles;
        }

        public IQueryable<Role> GetUserRoles()
        {
            List<int> ids = new List<int>() { 0, 2, 3 };

            return this.ObjectContext.Roles.Where(role => ids.Contains(role.RoleID));
        }

        public IQueryable<Role> GetRoleById(int id)
        {
            return this.ObjectContext.Roles.Where(role => role.RoleID == id);
        }

        public void InsertRole(Role role)
        {
            if ((role.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(role, EntityState.Added);
            }
            else
            {
                this.ObjectContext.Roles.AddObject(role);
            }
        }

        public void UpdateRole(Role currentRole)
        {
            this.ObjectContext.Roles.AttachAsModified(currentRole, this.ChangeSet.GetOriginal(currentRole));
        }

        public void DeleteRole(Role role)
        {
            if ((role.EntityState == EntityState.Detached))
            {
                this.ObjectContext.Roles.Attach(role);
            }
            this.ObjectContext.Roles.DeleteObject(role);
        }

        // TODO:
        // Consider constraining the results of your query method.  If you need additional input you can
        // add parameters to this method or create additional query methods with different names.
        // To support paging you will need to add ordering to the 'Settings' query.
        public IQueryable<Setting> GetSettings()
        {
            return this.ObjectContext.Settings;
        }

        public void InsertSetting(Setting setting)
        {
            if ((setting.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(setting, EntityState.Added);
            }
            else
            {
                this.ObjectContext.Settings.AddObject(setting);
            }
        }

        public void UpdateSetting(Setting currentSetting)
        {
            this.ObjectContext.Settings.AttachAsModified(currentSetting, this.ChangeSet.GetOriginal(currentSetting));
        }

        public void DeleteSetting(Setting setting)
        {
            if ((setting.EntityState == EntityState.Detached))
            {
                this.ObjectContext.Settings.Attach(setting);
            }
            this.ObjectContext.Settings.DeleteObject(setting);
        }

        // TODO:
        // Consider constraining the results of your query method.  If you need additional input you can
        // add parameters to this method or create additional query methods with different names.
        // To support paging you will need to add ordering to the 'Shifts' query.
        public IQueryable<Shift> GetShifts()
        {
            return this.ObjectContext.Shifts;
        }

        public int? GetShiftMaxId()
        {
            int? max = (from shift in this.ObjectContext.Shifts select shift.ShiftID).Max();

            return (max == null ? 0 : max) + 1;
        }

        public IQueryable<Shift> GetShiftById(int shiftID)
        {
            return this.ObjectContext.Shifts.Where(shift => shift.ID == shiftID);
        }

        public IQueryable<Shift> GetShiftsByCompanyID(int companyID)
        {
            return this.ObjectContext.Shifts.Where(sh => sh.CompanyID == companyID || sh.CompanyID == 0);
        }

        public void InsertShift(Shift shift)
        {
            if ((shift.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(shift, EntityState.Added);
            }
            else
            {
                this.ObjectContext.Shifts.AddObject(shift);
            }
        }

        public void UpdateShift(Shift currentShift)
        {
            this.ObjectContext.Shifts.AttachAsModified(currentShift, this.ChangeSet.GetOriginal(currentShift));
        }

        public void DeleteShift(Shift shift)
        {
            if ((shift.EntityState == EntityState.Detached))
            {
                this.ObjectContext.Shifts.Attach(shift);
            }
            this.ObjectContext.Shifts.DeleteObject(shift);
        }

        // TODO:
        // Consider constraining the results of your query method.  If you need additional input you can
        // add parameters to this method or create additional query methods with different names.
        // To support paging you will need to add ordering to the 'ShiftAssignments' query.
        public IQueryable<ShiftAssignment> GetShiftAssignments()
        {
            return this.ObjectContext.ShiftAssignments;
        }

        public ShiftAssignment GetShiftAssignmentByEmployeeID(int id)
        {
            return this.ObjectContext.ShiftAssignments.Where(sa => sa.EmployeeID == id).FirstOrDefault();
        }

        public void DestroyShiftAssignmentByEmployeeID(int id)
        {
            foreach (ShiftAssignment assign in this.ObjectContext.ShiftAssignments.Where(sa =>
                sa.EmployeeID == id))
            {
                if (DateTime.Now.Date >= assign.AssignmentFrom.Value.Date
                    && DateTime.Now.Date <= assign.AssignmentTo.Value.Date)
                {
                    foreach (ShiftAssignmentDay day in this.ObjectContext.ShiftAssignmentDays.Where(sad
                    => sad.ShiftAssignmentID == assign.ID))
                    {
                        this.ObjectContext.ShiftAssignmentDays.DeleteObject(day);
                    }

                    this.ObjectContext.ShiftAssignments.DeleteObject(assign);
                }
            }

            this.ObjectContext.SaveChanges();
        }

        public void InsertShiftAssignment(ShiftAssignment shiftAssignment)
        {
            if ((shiftAssignment.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(shiftAssignment, EntityState.Added);
            }
            else
            {
                this.ObjectContext.ShiftAssignments.AddObject(shiftAssignment);
            }
        }

        public void UpdateShiftAssignment(ShiftAssignment currentShiftAssignment)
        {
            this.ObjectContext.ShiftAssignments.AttachAsModified(currentShiftAssignment, this.ChangeSet.GetOriginal(currentShiftAssignment));
        }

        public void DeleteShiftAssignment(ShiftAssignment shiftAssignment)
        {
            if ((shiftAssignment.EntityState == EntityState.Detached))
            {
                this.ObjectContext.ShiftAssignments.Attach(shiftAssignment);
            }
            this.ObjectContext.ShiftAssignments.DeleteObject(shiftAssignment);
        }

        // TODO:
        // Consider constraining the results of your query method.  If you need additional input you can
        // add parameters to this method or create additional query methods with different names.
        // To support paging you will need to add ordering to the 'ShiftAssignmentDays' query.
        public IQueryable<ShiftAssignmentDay> GetShiftAssignmentDays()
        {
            return this.ObjectContext.ShiftAssignmentDays;
        }

        public void InsertShiftAssignmentDay(ShiftAssignmentDay shiftAssignmentDay)
        {
            if ((shiftAssignmentDay.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(shiftAssignmentDay, EntityState.Added);
            }
            else
            {
                this.ObjectContext.ShiftAssignmentDays.AddObject(shiftAssignmentDay);
            }
        }

        public void UpdateShiftAssignmentDay(ShiftAssignmentDay currentShiftAssignmentDay)
        {
            this.ObjectContext.ShiftAssignmentDays.AttachAsModified(currentShiftAssignmentDay, this.ChangeSet.GetOriginal(currentShiftAssignmentDay));
        }

        public void DeleteShiftAssignmentDay(ShiftAssignmentDay shiftAssignmentDay)
        {
            if ((shiftAssignmentDay.EntityState == EntityState.Detached))
            {
                this.ObjectContext.ShiftAssignmentDays.Attach(shiftAssignmentDay);
            }
            this.ObjectContext.ShiftAssignmentDays.DeleteObject(shiftAssignmentDay);
        }

        // TODO:
        // Consider constraining the results of your query method.  If you need additional input you can
        // add parameters to this method or create additional query methods with different names.
        // To support paging you will need to add ordering to the 'sysdiagrams' query.
        public IQueryable<sysdiagram> GetSysdiagrams()
        {
            return this.ObjectContext.sysdiagrams;
        }

        public void InsertSysdiagram(sysdiagram sysdiagram)
        {
            if ((sysdiagram.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(sysdiagram, EntityState.Added);
            }
            else
            {
                this.ObjectContext.sysdiagrams.AddObject(sysdiagram);
            }
        }

        public void UpdateSysdiagram(sysdiagram currentsysdiagram)
        {
            this.ObjectContext.sysdiagrams.AttachAsModified(currentsysdiagram, this.ChangeSet.GetOriginal(currentsysdiagram));
        }

        public void DeleteSysdiagram(sysdiagram sysdiagram)
        {
            if ((sysdiagram.EntityState == EntityState.Detached))
            {
                this.ObjectContext.sysdiagrams.Attach(sysdiagram);
            }
            this.ObjectContext.sysdiagrams.DeleteObject(sysdiagram);
        }

        // TODO:
        // Consider constraining the results of your query method.  If you need additional input you can
        // add parameters to this method or create additional query methods with different names.
        // To support paging you will need to add ordering to the 'Users' query.
        public IQueryable<User> GetUsers()
        {
            return this.ObjectContext.Users;
        }

        public int IsLoginTaken(string login)
        {
            return this.ObjectContext.Users.Where(user => user.Login.ToLower() == login.ToLower()).Count() > 0
                ? this.ObjectContext.Users.Where(user => user.Login.ToLower() == login.ToLower()).FirstOrDefault().UserID
                : 0;
        }

        public IQueryable<User> GetUserById(int id)
        {
            return this.ObjectContext.Users.Where(user => user.UserID == id);
        }

        public void DestroyUserById(int id)
        {
            this.ObjectContext.Users.DeleteObject(this.ObjectContext.Users.Where(user => user.UserID == id).FirstOrDefault());
        }

        public void LogoutUser()
        {
            FormsAuthentication.SignOut();
        }

        public LoginStatus GetLoginStatus(string userName, string password)
        {
            AttendanceDBEntities attendance = new AttendanceDBEntities();

            LoginStatus status = new LoginStatus(false, "Not Allowed");

            if (attendance.Users.Where(user => user.Login == userName && user.Password == password).Count() > 0)
            {
                status.LoggedIn = true;

                User currentUser = attendance.Users.Where(user1 => user1.Login == userName && user1.Password == password)
                    .FirstOrDefault();

                status.Role = currentUser.Role1.RoleName;

                status.Name = currentUser.Login;

                status.Display = currentUser.FirstName + " " + currentUser.LastName;

                if (currentUser.Role1.RoleName == "Administrator")
                {
                    status.CompanyID = this.ObjectContext.Companies.FirstOrDefault().ID;

                    status.DepartmentID = 0;

                    status.EmployeeID = 0;
                }

                if (currentUser.Role1.RoleName == "Company Manager")
                {
                    status.CompanyID = currentUser.CompanyID.Value;

                    status.DepartmentID = 0;

                    status.EmployeeID = 0;
                }

                if (currentUser.Role1.RoleName == "Department Manager")
                {
                    status.CompanyID = currentUser.CompanyID.Value;

                    status.DepartmentID = currentUser.DepartmentID.Value;

                    status.EmployeeID = 0;
                }

                FormsAuthentication.SetAuthCookie(userName, false);
            }

            if (attendance.Employees.Where(emp => emp.Login == userName && emp.Password == password && emp.Active!=null && emp.Active==true).Count() > 0)
            {
                status.LoggedIn = true;

                Employee currentEmp = attendance.Employees.Where(emp => emp.Login == userName && emp.Password == password)
                    .FirstOrDefault();

                status.Role = "Simple User";

                status.Name = currentEmp.Login;

                status.Display = currentEmp.FirstName + " " + currentEmp.LastName;

                status.CompanyID = currentEmp.CompanyID.Value;
                status.DepartmentID = currentEmp.DepartmentID.Value;
                status.EmployeeID = currentEmp.EmployeeID;

                FormsAuthentication.SetAuthCookie(userName, false);
            }

            return status;
        }

        public void InsertUser(User user)
        {
            if ((user.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(user, EntityState.Added);
            }
            else
            {
                this.ObjectContext.Users.AddObject(user);
            }
        }

        public void UpdateUser(User currentUser)
        {
            this.ObjectContext.Users.AttachAsModified(currentUser, this.ChangeSet.GetOriginal(currentUser));
        }

        public void DeleteUser(User user)
        {
            if ((user.EntityState == EntityState.Detached))
            {
                this.ObjectContext.Users.Attach(user);
            }
            this.ObjectContext.Users.DeleteObject(user);
        }

        // TODO:
        // Consider constraining the results of your query method.  If you need additional input you can
        // add parameters to this method or create additional query methods with different names.
        // To support paging you will need to add ordering to the 'Versions' query.
        public IQueryable<TVersion> GetVersions()
        {
            return this.ObjectContext.TVersions;
        }

        public void InsertVersion(TVersion version)
        {
            if ((version.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(version, EntityState.Added);
            }
            else
            {
                this.ObjectContext.TVersions.AddObject(version);
            }
        }

        public void UpdateVersion(LEEAAttendanceControl.Web.TVersion currentVersion)
        {
            this.ObjectContext.TVersions.AttachAsModified(currentVersion, this.ChangeSet.GetOriginal(currentVersion));
        }

        public void DeleteVersion(LEEAAttendanceControl.Web.TVersion version)
        {
            if ((version.EntityState == EntityState.Detached))
            {
                this.ObjectContext.TVersions.Attach(version);
            }
            this.ObjectContext.TVersions.DeleteObject(version);
        }

        // TODO:
        // Consider constraining the results of your query method.  If you need additional input you can
        // add parameters to this method or create additional query methods with different names.
        // To support paging you will need to add ordering to the 'WeekDays' query.

        public IQueryable<WeekDay> GetWeekDays()
        {
            return this.ObjectContext.WeekDays;
        }

        public void InsertWeekDay(WeekDay weekDay)
        {
            if ((weekDay.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(weekDay, EntityState.Added);
            }
            else
            {
                this.ObjectContext.WeekDays.AddObject(weekDay);
            }
        }

        public void UpdateWeekDay(WeekDay currentWeekDay)
        {
            this.ObjectContext.WeekDays.AttachAsModified(currentWeekDay, this.ChangeSet.GetOriginal(currentWeekDay));
        }

        public void DeleteWeekDay(WeekDay weekDay)
        {
            if ((weekDay.EntityState == EntityState.Detached))
            {
                this.ObjectContext.WeekDays.Attach(weekDay);
            }
            this.ObjectContext.WeekDays.DeleteObject(weekDay);
        }

        // TODO:
        // Consider constraining the results of your query method.  If you need additional input you can
        // add parameters to this method or create additional query methods with different names.
        // To support paging you will need to add ordering to the 'WorkTimes' query.

        public IQueryable<WorkTime> GetWorkTimes()
        {

            return this.ObjectContext.WorkTimes.Include("WeekDay");
        }

        public ObservableCollection<WorkTimeSummary> GetWorkTimesSummary(int companyId)
        {
            var q= from i in ObjectContext.WorkTimes
                   join w in ObjectContext.WeekDays on i.DayId equals w.DayID
                   where i.CompanyId==companyId
                   select new WorkTimeSummary { ID = i.ID,CompanyId=i.CompanyId.Value,DayId=i.DayId.Value, DayName = w.DayName,Time =SqlFunctions.StringConvert((double)i.Time)};
            ObservableCollection<WorkTimeSummary> lstWorkTimes = new ObservableCollection<WorkTimeSummary>();
            foreach (WorkTimeSummary ws in q)
            {
                ws.Time = ConvertMinutesToHours.Convert(ws.Time);
                lstWorkTimes.Add(ws); 
            }
            return lstWorkTimes;
        }

        public void UpdateWorkTimeSummary(WorkTimeSummary currentWorkTime)
        {
            WorkTime wt=new WorkTime();
            DateTime date =DateTime.Parse(currentWorkTime.Time);
            wt.ID = currentWorkTime.ID;
            wt.DayId = currentWorkTime.DayId;
            wt.CompanyId = currentWorkTime.CompanyId;
            wt.Time=(int)date.TimeOfDay.TotalMinutes;
            
            this.ObjectContext.WorkTimes.AttachAsModified(wt);
        }

        
        public void InsertWorkTime(WorkTime workTime)
        {
            if ((workTime.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(workTime, EntityState.Added);
            }
            else
            {
                this.ObjectContext.WorkTimes.AddObject(workTime);
            }
        }

        public void UpdateWorkTime(WorkTime currentWorkTime)
        {
            this.ObjectContext.WorkTimes.AttachAsModified(currentWorkTime, this.ChangeSet.GetOriginal(currentWorkTime));
        }

        public void DeleteWorkTime(WorkTime workTime)
        {
            if ((workTime.EntityState == EntityState.Detached))
            {
                this.ObjectContext.WorkTimes.Attach(workTime);
            }
            this.ObjectContext.WorkTimes.DeleteObject(workTime);
        }


        public IQueryable<Location> GetLocations()
        {
            return this.ObjectContext.Locations;
        }
        public IQueryable<Location> GetLocationById(int locationId)
        {
            return this.ObjectContext.Locations.Where(l=>l.LocationId==locationId);
        }

        public void InsertLocation(Location location)
        {
            if ((location.EntityState != EntityState.Detached))
            {
                this.ObjectContext.ObjectStateManager.ChangeObjectState(location, EntityState.Added);
            }
            else
            {
                this.ObjectContext.Locations.AddObject(location);
            }
        }

        public void UpdateLocation(Location currentLocation)
        {
            this.ObjectContext.Locations.AttachAsModified(currentLocation, this.ChangeSet.GetOriginal(currentLocation));
        }

        public void DeleteLocation(Location location)
        {
            if ((location.EntityState == EntityState.Detached))
            {
                this.ObjectContext.Locations.Attach(location);
            }
            this.ObjectContext.Locations.DeleteObject(location);
        }

    }
}